package net.tycmc.cty.ctyms.timejob.function.excel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import net.tycmc.bulb.common.util.MapGetter;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 导出Excel2007
 * @author chengshichao
 * 创建时间：2012-12-14
 * 注释：支持大数据量数据导出，导出数据上线未进行测试。测试导出：100000*500 花费时间240s左右
 * 更新记录：
 * 2012-12-19 chengshichao 修改临时文件输出时编码方式
 * 2013-03-30 haoyang 添加 默认格式:style7
 * 2013-04-06 chengshichao 数据导出完成后删除临时文件
 */
public class ExportHandlers2007 {
	
	private static ExportHandlers2007 instance;
	
	private ExportHandlers2007(){}
	
	public static ExportHandlers2007 getInstance(){
		if (instance == null) {
			synchronized(ExportHandlers2007.class){
				instance = new ExportHandlers2007();
			}
		}
		return instance;
	}
	
	/**
	 * 提供外部调用方法
	 * @param headList
	 * @param dataList
	 * @param headKey
	 * @param dataHeadKey
	 * @param paramKey
	 * @param out
	 */
	public void exportHandlers(List<Map<String,Object>> headList,List<Map<String,Object>> dataList,String headKey,String dataHeadKey,OutputStream out){
		XSSFWorkbook wb = new XSSFWorkbook();
    	XSSFSheet sheet = wb.createSheet("sheet");
    	Map<String, XSSFCellStyle> styles = createStyles(wb);
    	String sheetRef = sheet.getPackagePart().getPartName().getName();
    	
    	FileOutputStream os = null;
    	try {
    		os = new FileOutputStream("template.xlsx");
			wb.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		//Step 2. Generate XML file.生成一个xml文件
		File tmp = null;
		Writer fw = null;
		try {
			tmp = File.createTempFile("sheet", ".xml");
			fw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tmp),"UTF-8"));
			generate(fw, styles, headList, dataList, headKey, dataHeadKey);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				fw.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
        //Step 3. Substitute the template entry with the generated data
        try {
			substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if(tmp!=null && tmp.isFile() && tmp.exists()){
				tmp.delete();
			}
		}
	}
	
	
   private Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
	   Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
       XSSFDataFormat fmt = wb.createDataFormat();

       XSSFCellStyle style1 = wb.createCellStyle();
       style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
       style1.setDataFormat(fmt.getFormat("0.0%"));
       styles.put("percent", style1);

       XSSFCellStyle style2 = wb.createCellStyle();
       style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       style2.setDataFormat(fmt.getFormat("0.00"));
       styles.put("coeff", style2);

       XSSFCellStyle style3 = wb.createCellStyle();
       style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
       style3.setDataFormat(fmt.getFormat("$#,##0.00"));
       styles.put("currency", style3);

       XSSFCellStyle style4 = wb.createCellStyle();
       style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
       style4.setDataFormat(fmt.getFormat("mmm dd"));
       styles.put("date", style4);

       XSSFCellStyle style5 = wb.createCellStyle();
       XSSFFont headerFont = wb.createFont();
       headerFont.setBold(true);
       style5.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       style5.setFillForegroundColor(IndexedColors.RED.getIndex());
       style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
       style5.setFont(headerFont);
       styles.put("header", style5);
       
       XSSFCellStyle style6 = wb.createCellStyle();
       style6.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       style6.setDataFormat(fmt.getFormat("0"));
       styles.put("integer", style6);
       
       XSSFCellStyle style7 = wb.createCellStyle();
       style7.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       style7.setDataFormat(fmt.getFormat(""));
       styles.put("default", style7);//默认格式
       
       return styles;
   }

   private void generate(Writer out, Map<String, XSSFCellStyle> styles,List<Map<String,Object>> headList,List<Map<String,Object>> dataList,String headKey,String dataHeadKey) throws Exception {

       Calendar calendar = Calendar.getInstance();

       SpreadsheetWriter sw = new SpreadsheetWriter(out);
       sw.beginSheet();

       //insert header row
       sw.insertRow(0);
       int styleIndex = styles.get("header").getIndex();
       
       int headListLen = headList.size();
       for(int i = 0;i<headListLen;i++){
    	   Map<String,Object> headMap = headList.get(i);
    	   //2013.01.29 haoyang 修改--调用的方法更改了
    	   sw.createCell(i, MapGetter.getString(headMap, headKey), styleIndex,false);
       }
       sw.endRow();
       //write data rows
       int dataListLen = dataList.size();
       //数字格式! 0.00 保留两位小数
       styleIndex = styles.get("coeff").getIndex();
       for (int rownum = 1; rownum <=dataListLen; rownum++) {
    	   Map<String,Object> dataMap = dataList.get(rownum-1);
           sw.insertRow(rownum);
           for(int i = 0;i<headListLen;i++){
        	   if(headList.get(i).containsKey("cusStyle")){
        		   styleIndex = styles.get(MapGetter.getString(headList.get(i), "cusStyle")).getIndex();
        	   }
        	   //2013.01.29 haoyang 修改--excel 表格数据列表,考虑数字格式
        	   if(dataMap.containsKey(MapGetter.getString(headList.get(i), dataHeadKey)))
        		   sw.createCell(i, MapGetter.getString(dataMap, MapGetter.getString(headList.get(i), dataHeadKey)),styleIndex,true);
           }
           sw.endRow();
           calendar.roll(Calendar.DAY_OF_YEAR, 1);
       }
       sw.endSheet();
   }

  
   private  void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
       ZipFile zip = new ZipFile(zipfile);

       ZipOutputStream zos = new ZipOutputStream(out);

       @SuppressWarnings("unchecked")
       Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
       while (en.hasMoreElements()) {
           ZipEntry ze = en.nextElement();
           if(!ze.getName().equals(entry)){
               zos.putNextEntry(new ZipEntry(ze.getName()));
               InputStream is = zip.getInputStream(ze);
               copyStream(is, zos);
               is.close();
           }
       }
       zos.putNextEntry(new ZipEntry(entry));
       InputStream is = new FileInputStream(tmpfile);
       copyStream(is, zos);
       is.close();

       zos.close();
   }

   private void copyStream(InputStream in, OutputStream out) throws IOException {
       byte[] chunk = new byte[1024];
       int count;
       while ((count = in.read(chunk)) >=0 ) {
         out.write(chunk,0,count);
       }
   }

  
   private class SpreadsheetWriter {
       private final Writer _out;
       private int _rownum;

       public SpreadsheetWriter(Writer out){
           _out = out;
       }

       public void beginSheet() throws IOException {
           _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                   "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
           _out.write("<sheetData>\n");
       }

       public void endSheet() throws IOException {
           _out.write("</sheetData>");
           _out.write("</worksheet>");
       }

      
       public void insertRow(int rownum) throws IOException {
           _out.write("<row r=\""+(rownum+1)+"\">\n");
           this._rownum = rownum;
       }

      
       public void endRow() throws IOException {
           _out.write("</row>\n");
       }
       
       /**
        * @param columnIndex
        * @param value
        * @param styleIndex
        * @param conNum
        * @throws IOException
        * 2013.01.29 haoyang 修改,添加数字格式转换
        */
       public void createCell(int columnIndex, String value, int styleIndex,boolean conNum) throws IOException {
           String ref = new CellReference(_rownum, columnIndex).formatAsString();
           //如果考虑数字转换
           if(conNum){
        	   //符合 数字的格式
        	   if(value.matches("\\-?\\d+(\\.\\d+)?")){
        		   _out.write("<c r=\""+ref+"\"");
                   if(styleIndex != -1) 
                	   _out.write(" s=\""+styleIndex+"\"");
                   _out.write(">");
                   _out.write("<v>"+value+"</v>");
                   _out.write("</c>");
        	   }else{
        		   createCell(columnIndex,value,styleIndex,false);
        	   }
           }else{
        	   _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
        	   if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
        	   _out.write(">");
        	   _out.write("<is><t>"+value+"</t></is>");
        	   _out.write("</c>");
           }
       }

//       public void createCell(int columnIndex, String value) throws IOException {
//           createCell(columnIndex, value, -1,false);
//       }
   }
   
//   public static void main(String[] args) throws Exception {
//       // Step 1. Create a template file. Setup sheets and workbook-level objects such as
//       // cell styles, number formats, etc.
//   	XSSFWorkbook wb = new XSSFWorkbook();
//   	XSSFSheet sheet = wb.createSheet("Big Grid");
//
//       Map<String, XSSFCellStyle> styles = createStyles(wb);
//       //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
//       String sheetRef = sheet.getPackagePart().getPartName().getName();
//
//       //save the template  保存模板
//       FileOutputStream os = new FileOutputStream("template.xlsx");
//       wb.write(os);
//       os.close();
//
//       //Step 2. Generate XML file.生成一个xml文件
//       File tmp = File.createTempFile("sheet", ".xml");
//       Writer fw = new FileWriter(tmp);
//       generate(fw, styles);
//       fw.close();
//
//       //Step 3. Substitute the template entry with the generated data
//       FileOutputStream out = new FileOutputStream("E:/big-grid.xlsx");
//       substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
//       out.close();
//   }
}
